import math
import os
import random
import time

from flask import Flask, render_template, request, redirect, session, url_for, flash, jsonify
from sqlalchemy import or_, text

app = Flask(__name__)

# 导入 app 的基本配置
from app_config import BaseConfig

app.config.from_object(BaseConfig)

# 设置 static 文件夹的路径
# app.static_folder = os.path.join(app.root_path, 'static')

# 注册 mysql
from db_linker import db
from models import Movies, MovieGenreLink, Genres, User, UserGenreLink, UserScoreOfMovie, UserCommentsOfMovie, \
    CommentAgree, UserFavorOfMovie

# 注册 mysql插件
db.init_app(app)


def get_user_recommend():
    """获取用户 推荐电影"""

    if 'user' not in session:
        return None

    user_id = session['user']['id']

    # 找出 用户偏好的类型
    li_genres = [
        item.value
        for item in db.session.query(Genres).join(
            UserGenreLink
        ).filter(
            UserGenreLink.user_id == user_id
        ).all()
    ]

    # 找出用户 喜欢的电影的类型
    li_user_favor_movie_id = []
    for favor_movie in db.session.query(Movies).join(
            UserFavorOfMovie
    ).filter(
        UserFavorOfMovie.user_id == user_id
    ).all():
        li_user_favor_movie_id.append(favor_movie.id)  # 记录 喜欢的电影
        for link_item in favor_movie.movie_genre_link.all():  # 找出每个电影的所有分类
            genre_val = link_item.genre.value
            if genre_val not in li_genres:
                li_genres.append(genre_val)  # 记录

    # 根据记录的类型 推荐电影
    li_recommend_movie = db.session.query(Movies).join(
        MovieGenreLink
    ).join(
        Genres
    ).filter(
        Genres.value.in_(li_genres),  # 筛选 指定的类型
        Movies.id.notin_(li_user_favor_movie_id)  # 过滤掉已经喜欢的电影
    ).group_by(Movies.id).all()

    print('推荐电影：', li_recommend_movie)

    if len(li_recommend_movie) <= 10:
        return li_recommend_movie

    return random.sample(list(li_recommend_movie), 10)


@app.route('/')
def home():
    key = request.values.get('key')
    genre_id = request.values.get('genre_id')

    res_movies = db.session.query(Movies)

    # 搜索
    if key:
        res_movies = res_movies.filter(
            or_(
                Movies.name.ilike(f'%{key}%'),
                Movies.directors.ilike(f'%{key}%'),
                Movies.actors.ilike(f'%{key}%'),
                Movies.desc.ilike(f'%{key}%'),
            ).self_group()
        )

    # 筛选类型
    if genre_id:
        res_movies = res_movies.join(
            MovieGenreLink
        ).filter(
            MovieGenreLink.genre_id == genre_id
        )

    # 电影列表
    li_movies = res_movies.order_by(Movies.id.desc()).limit(20).all()

    # 种类列表
    li_genres = Genres.query.order_by(Genres.value).all()

    # 返回用户偏好
    li_user_favor_genres = []
    if 'user' in session:
        tmp = db.session.query(Genres).join(
            UserGenreLink
        ).filter(
            UserGenreLink.user_id == session['user']['id']
        )

        li_user_favor_genres = [
            item.genre_base_info_()
            for item in tmp
        ]

    return render_template('index.html',
                           li_movies=li_movies,
                           li_genres=li_genres,
                           li_recommend_movie=get_user_recommend(),
                           li_user_favor_genres=li_user_favor_genres)


@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'GET':
        return render_template('register.html')

    # 注册操作
    username = request.form.get('username')
    password = request.form.get('password')
    age = request.form.get('age')

    # 判断是否存在
    if User.query.filter(User.username == username).first():
        # 用户名已存在
        flash('Username Exist!', 'warning')
        return redirect(url_for('register'))

    # 创建用户
    db.session.add(
        User(
            username=username,
            password=password,
            age=age,
        )
    )
    db.session.commit()
    flash('Register Successfully!', 'success')
    return redirect(url_for('login'))  # 认证失败，重定向到 登录界面


@app.route('/login', methods=['GET', 'POST'])
def login():
    """
    登录
    """

    if request.method == 'GET':  # 请求 返回登录页面
        return render_template('login.html')

    username = request.form['username']
    password = request.form['password']

    user = User.query.filter(
        User.username == username,
        User.password == password
    ).first()  # 获取当前登录的用户

    # 判断账号密码是否正确
    if not user:
        flash('Account Or Password Error!', 'warning')
        return redirect(url_for('login'))  # 认证失败，重定向到 登录界面

    # 验证通过，保存登录状态，重定向到后台主页
    session['user'] = user.user_base_info()

    flash('Login Successfully!', 'success')
    return redirect('/')


@app.route('/logout', methods=['GET', 'POST'])
def logout():
    """注销"""

    if 'user' in session:
        del session['user']

    return redirect(url_for('login'))  # 重定向回首页


@app.route('/details/<movie_id>')
def get_details(movie_id):
    """详情"""

    db_movie = Movies.query.get(movie_id)
    if not db_movie:
        flash('Movie Not Exist', 'danger')
        return redirect('/')

    return render_template('details.html',
                           movie=db_movie)


@app.route('/favor_genres', methods=['POST'])
def favor_genres():
    """偏好的类型"""

    # 检测登录
    if 'user' not in session:
        return jsonify(
            {
                'code': -1,
                'msg': 'Please Login First!',
                'data': ''
            }
        )

    # 获取 user_id
    user_id = session['user']['id']

    # 偏好id列表
    li_genres_id = request.json.get('li_genres_id')

    db.session.execute(text('SET FOREIGN_KEY_CHECKS = 0;'))  # 关闭外键约束

    # 先清理旧数据，再绑定
    UserGenreLink.query.filter(
        UserGenreLink.user_id == user_id
    ).delete()

    # 绑定偏好
    for genre_id in li_genres_id:
        # 绑定用户偏好类型
        db.session.add(
            UserGenreLink(
                user_id=user_id,
                genre_id=genre_id
            )
        )
    # 提交
    db.session.commit()

    # 返回用户偏好
    li_genres = db.session.query(Genres).join(
        UserGenreLink
    ).filter(
        UserGenreLink.user_id == user_id
    )

    return jsonify(
        {
            'code': 0,
            'msg': '绑定成功',
            'data': [
                item.genre_base_info_()
                for item in li_genres
            ]
        }
    )


@app.route('/score', methods=['POST'])
def score_movie():
    """打分"""

    # 判断是否登录
    if 'user' not in session:
        return jsonify({
            'code': -1,
            'msg': 'Please Login First!',
            'data': False
        })

    user_id = session['user']['id']

    movie_id = request.json.get('movie_id')
    score = request.json.get('score')

    # 判断是否已经打过分
    if UserScoreOfMovie.query.filter(
            UserScoreOfMovie.user_id == user_id,
            UserScoreOfMovie.movie_id == movie_id
    ).first():
        return jsonify({
            'code': 1,
            'msg': 'Already Scored',
            'data': False
        })

    # 打分
    db.session.add(
        UserScoreOfMovie(
            user_id=user_id,
            movie_id=movie_id,
            score=score
        )
    )
    db.session.commit()

    return jsonify({
        'code': 0,
        'msg': 'Score Successfully',
        'data': Movies.query.get(movie_id).get_avg_score()
    })


@app.route('/comment_list', methods=['GET'])
def get_comment_list():
    page = int(request.values.get('page', 1))
    sort = request.values.get('sort', 'sort_by_time')
    movie_id = request.values.get('movie_id')

    size = 10

    start = (page - 1) * size

    res = UserCommentsOfMovie.query.filter(
        UserCommentsOfMovie.movie_id == movie_id
    )

    # 排序
    if sort == 'sort_by_agree':  # 根据 点赞排序，需要 join 关联查询
        res = res.order_by(
            UserCommentsOfMovie.agree.desc()
        )
    else:  # 其余 默认 按 时间排序
        res = res.order_by(
            UserCommentsOfMovie.created_at.desc()
        )

    # 列表
    li_comments = res.offset(start).limit(size).all()
    # 统计
    count = res.count()

    return jsonify({
        'code': 0,
        'msg': 'Comment List',
        'data': {
            'page': page,
            'size': size,
            'pages': math.ceil(count / 10),
            'list': [
                item.comment_base_info()
                for item in li_comments
            ],
            'count': count
        }
    })


@app.route('/add_comment', methods=['POST'])
def add_comment():
    """添加一条评论"""

    # 判断是否登录
    if 'user' not in session:
        return jsonify({
            'code': -1,
            'msg': 'Please Login First!',
            'data': False
        })

    user_id = session['user']['id']

    movie_id = request.json.get('movie_id')
    content = request.json.get('content')

    db.session.add(
        UserCommentsOfMovie(
            user_id=user_id,
            movie_id=movie_id,
            content=content
        )
    )
    db.session.commit()
    return jsonify({
        'code': 0,
        'msg': 'Comment Successfully',
        'data': True
    })


@app.route('/agree_comment', methods=['POST'])
def agree_comment():
    """评论 赞/踩"""

    # 判断是否登录
    if 'user' not in session:
        return jsonify({
            'code': -1,
            'msg': 'Please Login First!',
            'data': False
        })

    user_id = session['user']['id']

    comment_id = request.json.get('comment_id')
    agree = request.json.get('agree')

    # 评论id
    db_comment = UserCommentsOfMovie.query.get(comment_id)

    # 查看是否已经有 赞/踩
    db_agree = CommentAgree.query.filter(
        CommentAgree.comment_id == comment_id,
        CommentAgree.user_id == user_id
    ).first()
    if db_agree:  # 存在，修改
        db_agree.agree = agree
    else:  # 不存在，添加
        db.session.add(
            CommentAgree(
                comment_id=comment_id,
                user_id=user_id,
                agree=agree,
            )
        )

    # 评论 更新最新 赞/踩
    db_comment.agree = CommentAgree.query.filter(CommentAgree.comment_id == comment_id,
                                                 CommentAgree.agree == True).count()

    db_comment.disagree = CommentAgree.query.filter(CommentAgree.comment_id == comment_id,
                                                    CommentAgree.agree == False).count()

    db.session.commit()
    return jsonify({
        'code': 0,
        'msg': 'Agree/Disagree Successfully',
        'data': True
    })


@app.route('/favor', methods=['GET', 'POST'])
def favor_movie():
    """喜欢-电影"""

    # 判断是否登录
    if 'user' not in session:
        return jsonify({
            'code': -1,
            'msg': 'Please Login First!',
            'data': False
        })

    user_id = session['user']['id']

    if request.method == 'GET':
        li_favor_movie = db.session.query(Movies).join(
            UserFavorOfMovie
        ).filter(
            UserFavorOfMovie.user_id == user_id
        ).all()
        return render_template('my_favor.html',
                               li_favor_movie=li_favor_movie)

    movie_id = request.json.get('movie_id')

    db_favor = UserFavorOfMovie.query.filter(
        UserFavorOfMovie.user_id == user_id,
        UserFavorOfMovie.movie_id == movie_id
    ).first()

    if db_favor:  # 存在，则 取消喜欢
        db.session.delete(db_favor)
    else:  # 不存在，则 喜欢
        db.session.add(
            UserFavorOfMovie(
                user_id=user_id,
                movie_id=movie_id
            )
        )

    db.session.commit()

    # 最新的 喜欢 状态
    now_favor = UserFavorOfMovie.query.filter(
        UserFavorOfMovie.user_id == user_id,
        UserFavorOfMovie.movie_id == movie_id
    ).first()

    return jsonify({
        'code': 0,
        'msg': 'Favor Successfully',
        'data': not not now_favor
    })


# ================ admin 后台管理 ================


@app.route('/admin/login', methods=['GET', 'POST'])
def admin_login():
    """
    管理员登录
    """

    if request.method == 'GET':  # 请求 返回登录页面
        return render_template('admin/admin_login.html')

    username = request.form['username']
    password = request.form['password']

    if username == 'admin' and password == 'admin':
        session['admin'] = 'admin'  # 保存 session
        flash('Login Successfully!', 'success')
        return redirect('/admin')

    flash('Username Or Password Error', 'danger')
    return redirect('/admin/login')


@app.route('/admin/logout', methods=['GET', 'POST'])
def admin_logout():
    """管理员注销"""

    if 'admin' in session:
        del session['admin']

    return redirect('/admin/login')  # 重定向回首页


@app.route('/admin', methods=['GET'])
def admin_index():
    """后台管理首页"""

    # 判断管理员是否登录
    if 'admin' not in session:
        flash('Please Login As Admin!', 'danger')
        return redirect('/admin/login')

    page = int(request.values.get('page', 1))
    size = int(request.values.get('size', 10))

    start = (page - 1) * size

    res = Movies.query

    return render_template(
        'admin/home.html',
        movies={
            'list': res.order_by(Movies.id.desc()).offset(start).limit(size).all(),
            'count': res.count(),
            'page': page
        }
    )


@app.route('/admin_movie_add', methods=['GET', 'POST'])
def admin_movie_add():
    """后台-电影-添加"""

    # 判断管理员是否登录
    if 'admin' not in session:
        flash('Please Login As Admin!', 'danger')
        return redirect('/admin/login')

    # 种类列表
    li_genres = Genres.query.order_by(Genres.value).all()

    if request.method == 'GET':
        return render_template('admin/admin_movie_add.html',
                               li_genres=li_genres)

    name = request.form['name']
    genres = request.form.getlist('genres')
    directors = request.form['directors']
    actors = request.form['actors']
    desc = request.form['desc']
    year = request.form['year']

    img = request.files.get('img')

    img_path = ''
    if img:
        # 资源文件夹
        save_dir = os.path.join(
            app.root_path,
            'static',
            'imgs',
            'movies',
            f'{int(time.time())}'
        )

        if not os.path.exists(save_dir):
            os.mkdir(save_dir)

        # 保存文件，获取地址
        file_name = img.filename  # 文件名

        # 保存的完整路径
        file_full_path = os.path.join(
            save_dir,
            file_name
        )
        # 保存文件到本地
        img.save(file_full_path)

        # 资源路径
        img_path = os.path.join(
            'imgs',
            'movies',
            f'{int(time.time())}',
            file_name
        )

    # 添加
    db_movie = Movies(
        name=name,
        directors=directors,
        actors=actors,
        desc=desc,
        year=year,
        image_path=img_path,
    )

    db.session.add(db_movie)
    db.session.flush()
    movie_id = db_movie.id  # 电影id

    for genres_id in genres:
        db.session.add(
            MovieGenreLink(
                movie_id=movie_id,
                genre_id=genres_id
            )
        )

    db.session.commit()
    flash('Add Successfully!', 'success')
    return redirect('/admin')


@app.route('/admin_movie_update/<int:mid>', methods=['GET', 'POST'])
def admin_movie_update(mid):
    """后台-电影-修改"""

    # 判断管理员是否登录
    if 'admin' not in session:
        flash('Please Login As Admin!', 'danger')
        return redirect('/admin/login')

    db_movie = Movies.query.get(mid)
    if not db_movie:
        return redirect('/admin')

    # 种类列表
    li_genres = Genres.query.order_by(Genres.value).all()

    if request.method == 'GET':
        return render_template('admin/admin_movie_update.html',
                               movie=db_movie,
                               li_genres=li_genres)

    name = request.form['name']
    genres = request.form.getlist('genres')
    directors = request.form['directors']
    actors = request.form['actors']
    desc = request.form['desc']
    year = request.form['year']

    img = request.files.get('img')

    if img:
        # 资源文件夹
        save_dir = os.path.join(
            app.root_path,
            'static',
            'imgs',
            'movies',
            f'{int(time.time())}'
        )

        if not os.path.exists(save_dir):
            os.mkdir(save_dir)

        # 保存文件，获取地址
        file_name = img.filename  # 文件名

        # 保存的完整路径
        file_full_path = os.path.join(
            save_dir,
            file_name
        )
        # 保存文件到本地
        img.save(file_full_path)

        # 资源路径
        img_path = os.path.join(
            'imgs',
            'movies',
            f'{int(time.time())}',
            file_name
        )
        # 修复 window 下 路径问题
        img_path = img_path.replace('\\', '/')

        # 修改图片
        db_movie.image_path = img_path

    # 修改
    db_movie.name = name
    db_movie.directors = directors
    db_movie.actors = actors
    db_movie.desc = desc
    db_movie.year = year

    db.session.execute(text('SET FOREIGN_KEY_CHECKS = 0;'))  # 关闭外键约束

    # 删除所有绑定
    MovieGenreLink.query.filter(
        MovieGenreLink.movie_id == mid
    ).delete()

    # 重新绑定
    for genres_id in genres:
        db.session.add(
            MovieGenreLink(
                movie_id=mid,
                genre_id=genres_id
            )
        )

    db.session.commit()
    flash('Update Successfully!', 'success')
    return redirect('/admin')


@app.route('/admin_movie_del/<int:mid>', methods=['GET', 'POST'])
def admin_movie_del(mid):
    """后台-电影-删除"""

    # 判断管理员是否登录
    if 'admin' not in session:
        flash('Please Login As Admin!', 'danger')
        return redirect('/admin/login')

    db_movie = Movies.query.get(mid)
    if not db_movie:
        return redirect('/admin')

    db.session.execute(text('SET FOREIGN_KEY_CHECKS = 0;'))  # 关闭外键约束

    # 删除
    db.session.delete(db_movie)

    db.session.commit()
    flash('Del Success', 'success')
    return redirect('/admin')


@app.route('/admin_user_list', methods=['GET'])
def admin_user_list():
    """后台-用户-列表"""

    # 判断管理员是否登录
    if 'admin' not in session:
        flash('Please Login As Admin!', 'danger')
        return redirect('/admin/login')

    page = int(request.values.get('page', 1))
    size = int(request.values.get('size', 10))

    start = (page - 1) * size

    res = User.query

    return render_template(
        'admin/admin_user_list.html',
        users={
            'list': res.order_by(User.id.desc()).offset(start).limit(size).all(),
            'count': res.count(),
            'page': page
        }
    )


@app.route('/admin_user_add', methods=['GET', 'POST'])
def admin_user_add():
    """后台-用户-添加"""

    # 判断管理员是否登录
    if 'admin' not in session:
        flash('Please Login As Admin!', 'danger')
        return redirect('/admin/login')

    if request.method == 'GET':
        return render_template('admin/admin_user_add.html')

    username = request.form['username']
    password = request.form.getlist('password')
    age = request.form['age']

    # 判存在
    if User.query.filter(
            User.username == username
    ).first():
        flash('Username Exist!', 'warning')
        return redirect('/admin_user_add')

    # 添加
    db.session.add(
        User(
            username=username,
            password=password,
            age=age,
        )
    )

    db.session.commit()
    flash('Add Successfully!', 'success')
    return redirect('/admin_user_list')


@app.route('/admin_user_update/<int:user_id>', methods=['GET', 'POST'])
def admin_user_update(user_id):
    """后台-用户-修改"""

    # 判断管理员是否登录
    if 'admin' not in session:
        flash('Please Login As Admin!', 'danger')
        return redirect('/admin/login')

    db_user = User.query.get(user_id)
    if not db_user:
        return redirect('/admin')

    if request.method == 'GET':
        return render_template('admin/admin_user_update.html',
                               user=db_user)

    username = request.form['username']
    password = request.form.getlist('password')
    age = request.form['age']

    # 修改
    db_user.username = username
    db_user.password = password
    db_user.age = age

    db.session.commit()
    flash('Update Successfully!', 'success')
    return redirect('/admin_user_list')


@app.route('/admin_user_del/<int:mid>', methods=['GET', 'POST'])
def admin_user_del(mid):
    """后台-用户-删除"""

    # 判断管理员是否登录
    if 'admin' not in session:
        flash('Please Login As Admin!', 'danger')
        return redirect('/admin/login')

    db_user = User.query.get(mid)
    if not db_user:
        return redirect('/admin_user_list')

    db.session.execute(text('SET FOREIGN_KEY_CHECKS = 0;'))  # 关闭外键约束

    # 删除
    db.session.delete(db_user)

    db.session.commit()
    flash('Del Success', 'success')
    return redirect('/admin_user_list')


@app.route('/admin_comment_list', methods=['GET'])
def admin_comment_list():
    """后台-评论-列表"""

    # 判断管理员是否登录
    if 'admin' not in session:
        flash('Please Login As Admin!', 'danger')
        return redirect('/admin/login')

    page = int(request.values.get('page', 1))
    size = int(request.values.get('size', 10))

    start = (page - 1) * size

    res = UserCommentsOfMovie.query

    return render_template(
        'admin/admin_comment_list.html',
        comments={
            'list': res.order_by(UserCommentsOfMovie.id.desc()).offset(start).limit(size).all(),
            'count': res.count(),
            'page': page
        }
    )


@app.route('/admin_comment_update/<int:comment_id>', methods=['GET', 'POST'])
def admin_comment_update(comment_id):
    """后台-评论-修改"""

    # 判断管理员是否登录
    if 'admin' not in session:
        flash('Please Login As Admin!', 'danger')
        return redirect('/admin/login')

    db_comment = UserCommentsOfMovie.query.get(comment_id)
    if not db_comment:
        return redirect('/admin_comment_list')

    if request.method == 'GET':
        return render_template('admin/admin_comment_update.html',
                               comment=db_comment)

    content = request.form['content']

    # 修改
    db_comment.content = content

    db.session.commit()
    flash('Update Successfully!', 'success')
    return redirect('/admin_comment_list')


@app.route('/admin_comment_del/<int:comment_id>', methods=['GET', 'POST'])
def admin_comment_del(comment_id):
    """后台-评论-删除"""

    # 判断管理员是否登录
    if 'admin' not in session:
        flash('Please Login As Admin!', 'danger')
        return redirect('/admin/login')

    db_comment = UserCommentsOfMovie.query.get(comment_id)
    if not db_comment:
        return redirect('/admin_comment_list')

    db.session.execute(text('SET FOREIGN_KEY_CHECKS = 0;'))  # 关闭外键约束

    # 删除
    db.session.delete(db_comment)

    db.session.commit()
    flash('Del Success', 'success')
    return redirect('/admin_comment_list')


if __name__ == '__main__':
    app.run(debug=True, port=5088)
